package zy.dao.money.bank.impl;

import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import zy.dao.BaseDaoImpl;
import zy.dao.money.bank.BankDAO;
import zy.entity.money.bank.T_Money_Bank;
import zy.util.CommonUtil;

@Repository
public class BankDAOImpl extends BaseDaoImpl implements BankDAO{
	@Override
	public List<T_Money_Bank> list(Map<String, Object> param) {
		Object searchContent = param.get("searchContent");
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT ba_id,ba_code,ba_name,ba_spell,ba_remark,ba_init_balance,ba_balance,ba_shop_code,companyid");
		sql.append(" FROM t_money_bank t");
		sql.append(" WHERE 1 = 1");
        if(null != searchContent && !"".equals(searchContent)){
        	sql.append(" AND (INSTR(t.ba_code,:searchContent)>0 OR INSTR(t.ba_name,:searchContent)>0 OR INSTR(t.ba_spell,:searchContent)>0)");
        }
        sql.append(" AND ba_shop_code = :shop_code ");//上级店铺编号
		sql.append(" AND t.companyid=:companyid");
		sql.append(" ORDER BY ba_id DESC");
		return namedParameterJdbcTemplate.query(sql.toString(), param, new BeanPropertyRowMapper<>(T_Money_Bank.class));
	}

	@Override
	public List<T_Money_Bank> listShop(Map<String, Object> param) {
		Object searchContent = param.get("searchContent");
		Object shop_upcode = param.get(CommonUtil.SHOP_UPCODE);
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT ba_id,ba_code,ba_name,ba_balance");
		sql.append(" FROM t_money_bank t");
		sql.append(" WHERE 1 = 1");
        if(null != searchContent && !"".equals(searchContent)){
        	sql.append(" AND INSTR(t.ba_spell,:searchContent)>0");
        }
        if(null != shop_upcode && !"".equals(shop_upcode)){
        	sql.append(" AND t.ba_shop_code=:shop_upcode");
        }
		sql.append(" AND t.companyid=:companyid");
		sql.append(" ORDER BY ba_id DESC");
		return namedParameterJdbcTemplate.query(sql.toString(), param, new BeanPropertyRowMapper<>(T_Money_Bank.class));
	}

	@Override
	public T_Money_Bank queryByID(Integer ba_id) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT ba_id,ba_code,ba_name,ba_spell,ba_remark,ba_init_balance,ba_balance,ba_shop_code,companyid ");
		sql.append(" FROM t_money_bank");
		sql.append(" WHERE ba_id=:ba_id");
		sql.append(" LIMIT 1");
		try{
			return namedParameterJdbcTemplate.queryForObject(sql.toString(), new MapSqlParameterSource().addValue("ba_id", ba_id),
					new BeanPropertyRowMapper<>(T_Money_Bank.class));
		}catch(Exception e){
			return null;
		}
	}
	
	@Override
	public T_Money_Bank queryByCode(String ba_code, Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT ba_id,ba_code,ba_name,ba_spell,ba_remark,ba_init_balance,IFNULL(ba_balance,0) AS ba_balance,ba_shop_code,companyid ");
		sql.append(" FROM t_money_bank");
		sql.append(" WHERE ba_code=:ba_code");
		sql.append(" AND companyid=:companyid");
		sql.append(" LIMIT 1");
		try{
			return namedParameterJdbcTemplate.queryForObject(sql.toString(), new MapSqlParameterSource().addValue("ba_code", ba_code).addValue("companyid", companyid),
					new BeanPropertyRowMapper<>(T_Money_Bank.class));
		}catch(Exception e){
			return null;
		}
	}

	@Override
	public void save(T_Money_Bank bank) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT f_three_code(max(ba_code+0)) FROM t_money_bank ");
		sql.append(" WHERE 1=1");
		sql.append(" AND companyid=:companyid");
		String code = namedParameterJdbcTemplate.queryForObject(sql.toString(), new BeanPropertySqlParameterSource(bank), String.class);
		bank.setBa_code(code);
		sql.setLength(0);
		sql.append("INSERT INTO t_money_bank");
		sql.append(" (ba_code,ba_name,ba_spell,ba_remark,ba_init_balance,ba_balance,ba_shop_code,companyid)");
		sql.append(" VALUES(:ba_code,:ba_name,:ba_spell,:ba_remark,:ba_init_balance,:ba_balance,:ba_shop_code,:companyid)");
		KeyHolder holder = new GeneratedKeyHolder(); 
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(bank),holder);
		int id = holder.getKey().intValue();
		bank.setBa_id(id);
	}

	@Override
	public void update(T_Money_Bank bank) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" UPDATE t_money_bank");
		sql.append(" SET ba_name=:ba_name");
		sql.append(" ,ba_spell=:ba_spell");
		sql.append(" ,ba_remark=:ba_remark");
		if(bank.getBa_init_balance() != null){
			sql.append(" ,ba_init_balance=:ba_init_balance");
		}
		sql.append(" WHERE ba_id=:ba_id");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(bank));
	}
	
	@Override
	public void updateBalanceById(T_Money_Bank bank) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" UPDATE t_money_bank");
		sql.append(" SET ba_balance = :ba_balance");
		sql.append(" WHERE ba_id=:ba_id");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(bank));
	}
	
	@Override
	public void updateBalanceById(List<T_Money_Bank> banks) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" UPDATE t_money_bank");
		sql.append(" SET ba_balance = :ba_balance");
		sql.append(" WHERE ba_id=:ba_id");
		namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(banks.toArray()));
	}

	@Override
	public void del(Integer ba_id) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" DELETE FROM t_money_bank");
		sql.append(" WHERE ba_id=:ba_id");
		namedParameterJdbcTemplate.update(sql.toString(), new MapSqlParameterSource().addValue("ba_id", ba_id));
	}

}
